﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
//using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;
using System.IO;
using System.Diagnostics;


namespace template_dll
{
    public partial class ChildForm : Form
    {
        public ChildForm()
        {
            InitializeComponent();
        }

        private SqlConnection pharmolapdw = new SqlConnection();
        private SqlConnection allpricedw = new SqlConnection();
        public DataTable dt_drugs;
        public DataTable dt_brand;
        public DataTable dt_suppl;
        public DataTable dt_pharm;
        public DataTable dt_drug_tmp;
        public DataTable dt_rep = new DataTable();
        private const string namechcol = "Флаг";
        private Guid guid;
        private delegate void Deleg();
        private Deleg del;
        private bool showres = true;
        private int cur_baseid = 0;
        private DataTable cur_cityid = new DataTable();
        private DataTable cur_supl = new DataTable();
        private string sp_name = "";
        private object ob_dtpiker = null;

        private string GetServ(string filename)
        {
            string str = "";
            using (StreamReader streamReader = new StreamReader(filename))
            {
                while (!streamReader.EndOfStream)
                {
                    str += streamReader.ReadLine();
                }
            }
            return str;
        }

        public static bool DisplayForm(System.Windows.Forms.Form theMDIParent)
        {
            ChildForm f = new ChildForm();
            f.MdiParent = theMDIParent;
            f.WindowState = FormWindowState.Maximized;
            f.Show();
            return true;
        }

        private void button1_Click(object sender, EventArgs e)
        {
            DrugsFilter.DrugsFilter f2 = new DrugsFilter.DrugsFilter();
            DrugsFilter.DrugsFilter.StorProcParam[] spp = new DrugsFilter.DrugsFilter.StorProcParam[1];

            spp[0] = f2.StorProcParamIns("@arch", SqlDbType.VarChar, 20, "jhjhjh");

            SqlCommand myCommand = new SqlCommand();
            myCommand = f2.StorProc(myCommand, pharmolapdw, "pas_AllDrugsSel", spp);

            if (dt_drugs == null)
            {
                try
                {
                    if (pharmolapdw.State == ConnectionState.Closed) pharmolapdw.Open();
                    dt_drugs = new DataTable();
                    dt_drugs.Load(myCommand.ExecuteReader());
                    foreach (DataColumn dc in dt_drugs.Columns)
                    {
                        if (dc.ColumnName != namechcol) dc.ReadOnly = true;
                        else dc.ReadOnly = false;
                    }
                    dt_brand = new DataTable();
                    dt_brand.Load(GetBrandTree(pharmolapdw));
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.ToString());
                }
                pharmolapdw.Close();
                f2.rb = true;
            }
            if (dt_drug_tmp == null)
            {
                try
                {
                    if (pharmolapdw.State == ConnectionState.Closed) pharmolapdw.Open();
                    dt_drug_tmp = new DataTable();
                    dt_drug_tmp.Load(myCommand.ExecuteReader());
                    foreach (DataColumn dc in dt_drug_tmp.Columns)
                    {
                        if (dc.ColumnName != namechcol) dc.ReadOnly = true;
                        else dc.ReadOnly = false;
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.ToString());
                }
                pharmolapdw.Close();
            }
            f2.sqlconn = pharmolapdw;
            f2.namechcol = namechcol;
            f2.dt_drug = dt_drugs;
            f2.dt_drug_tmp = dt_drug_tmp;
            if (dt_drug_tmp.Rows.Count == 0)
                f2.dataGridView1.DataSource = f2.dt_drug.DefaultView;
            else
                f2.dataGridView1.DataSource = f2.dt_drug_tmp.DefaultView;
            f2.dt_br = dt_brand;
            f2.ShowDialog();
            listBox1.Items.Clear();
            foreach (DataRow dr in dt_drug_tmp.Rows)
            {
                if (Convert.ToBoolean(dr[namechcol]) == true && dr.RowState != DataRowState.Deleted) listBox1.Items.Add(dr["Товар"]);
            }
            if (listBox1.Items.Count == 0) listBox1.Items.Add("(Все товары)");
            f2.Close();

        }

        private void ChildForm_Load(object sender, EventArgs e)
        {
            try
            {
                pharmolapdw.ConnectionString = @"Server=" + GetServ("serv.txt") + ";Database=PharmOLAPDW;Integrated Security=True";
                allpricedw.ConnectionString = @"Server=" + GetServ("serv.txt") + ";Database=AllPriceDW;Integrated Security=True";
                cur_cityid.Columns.Add("cityid");
                cur_supl.Columns.Add("supcode");
                listBox1.Items.Add("(Все товары)");
                listBox2.Items.Add("(Все поставщики)");
                showres = checkBox1.Checked;
                guid = Guid.NewGuid();
                dataGridView1.DataSource = dt_rep.DefaultView;
                dateTimePicker1.Value = DateTime.Now.AddDays(-1).Date;
                dateTimePicker2.Value = DateTime.Now.AddDays(-1).Date;
                dateTimePicker3.Value = DateTime.Now.AddDays(-1).Date;

                pharmolapdw.Open();
                SqlCommand com = pharmolapdw.CreateCommand();
                com.CommandText = "pas_BaseMASel";
                DataTable dt_base = new DataTable();
                dt_base.Load(com.ExecuteReader());

                if (dt_base.Rows.Count > 0)
                    foreach (DataRow row in dt_base.Rows)
                    {
                        NewItem ni = new NewItem();
                        ni.ParamID = Convert.ToInt32(row[0]);
                        ni.ParamComment = row[1].ToString();
                        comboBox1.Items.Add(ni);
                    }
                comboBox3.Items.Add("Цены поставщиков");
                comboBox3.Items.Add("Отклонение цен поставщиков");
                label3.Visible = false;
                dateTimePicker3.Visible = false;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }



        private void button4_Click(object sender, EventArgs e)
        {
            try
            {
                if (comboBox3.SelectedIndex > -1)
                {
                    if (comboBox1.SelectedIndex > -1)
                    {
                        if (checkedListBox1.SelectedItems.Count > 0)
                        {
                            cur_baseid = (comboBox1.SelectedItem as NewItem).ParamID;
                            for (int i = 0; i < checkedListBox1.CheckedItems.Count; i++)
                                cur_cityid.Rows.Add((checkedListBox1.CheckedItems[i] as NewItem).ParamID);
                            dataGridView1.DataSource = null;
                            dt_rep.Clear();
                            ob_dtpiker = null;
                            sp_name = "";
                            if (comboBox3.SelectedIndex == 0) sp_name = "pas_PriceSupReport";

                            if (comboBox3.SelectedIndex == 1)
                            {
                                sp_name = "pas_PriceSupReportDelta";
                                ob_dtpiker = dateTimePicker3;
                            }

                            cur_supl.Clear();
                            if(dt_suppl != null)
                                foreach (DataRow row in dt_suppl.Rows)
                                {
                                    if (Convert.ToBoolean(row[0]))
                                        cur_supl.Rows.Add(Convert.ToInt32(row[1]));
                                }
                            dataGridView1.DataSource = null;
                            del = new Deleg(CreateRep);
                            this.Text += " Формируется";
                            IAsyncResult asyncRes = del.BeginInvoke(new AsyncCallback(CallBackFunc), null); //
                            panel1.Enabled = false;
                            dataGridView1.Enabled = false;
                        }
                        else
                        {
                            MessageBox.Show("Не выбраны города");
                            checkedListBox1.Focus();
                        }
                    }
                    else
                    {
                        MessageBox.Show("Не выбрана база");
                        comboBox1.Focus();
                    }
                }
                else
                {
                    MessageBox.Show("Не выбран отчет");
                    comboBox3.Focus();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void CallBackFunc(IAsyncResult aRes)
        {
            del.EndInvoke(aRes);
            panel1.Invoke((Func<bool>)(() => panel1.Enabled = true));
            dataGridView1.Invoke((Func<bool>)(() => dataGridView1.Enabled = true));
            this.Invoke((Func<string>)(() => this.Text = this.Text.Replace(" Формируется", "")));
            statusStrip1.Invoke((Func<string>)(() => toolStripStatusLabel1.Text = "Всего строк: " + dt_rep.Rows.Count.ToString()));
            if (showres)
            {
                dataGridView1.Invoke((Func<object>)(() => dataGridView1.DataSource = dt_rep.DefaultView));                
            }
            else MessageBox.Show("Отчет сформирован");
        }

        private void CreateRep()
        {
            if (allpricedw.State == ConnectionState.Closed) allpricedw.Open();
            try
            {
                DataTable dt_dr = new DataTable();
                dt_dr.Columns.Add("drugcode");
                if (dt_drug_tmp != null)
                {

                    foreach (DataRow dr in dt_drug_tmp.Rows)
                        if (Convert.ToBoolean(dr[namechcol]) == true)
                        {
                            dt_dr.Rows.Add(dr[1]);
                        }
                }
                
                dt_rep.Clear();
                dt_rep = null;
                dt_rep = new DataTable();
                dt_rep.Load(RepProc(allpricedw, sp_name, dateTimePicker1, dateTimePicker2, dt_dr, cur_cityid, cur_supl, cur_baseid, ob_dtpiker));
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }

        private SqlDataReader RepProc(SqlConnection sqlcon, string procname, DateTimePicker dtp1, DateTimePicker dtp2, DataTable drug, DataTable city, DataTable suppl, int baseid, object date)
        {
            SqlCommand sqlcom = sqlcon.CreateCommand();
            sqlcom.CommandType = CommandType.StoredProcedure;
            sqlcom.CommandText = procname;
            sqlcom.CommandTimeout = 300;

            sqlcom.Parameters.AddWithValue("@datebeg", dtp1.Value);
            sqlcom.Parameters.AddWithValue("@dateend", dtp2.Value);
            sqlcom.Parameters.AddWithValue("@drugscode", drug);
            sqlcom.Parameters.AddWithValue("@cityid", city);
            sqlcom.Parameters.AddWithValue("@supcode", suppl);
            sqlcom.Parameters.AddWithValue("@baseid", baseid);
            if (date != null) sqlcom.Parameters.AddWithValue("@date", (date as DateTimePicker).Value);
            return sqlcom.ExecuteReader();
        }

        private SqlDataReader GetBrandTree(SqlConnection sqlcon)
        {
            SqlCommand com = sqlcon.CreateCommand();
            com.CommandType = CommandType.StoredProcedure;
            com.CommandText = @"pas_BrandTreeSel";

            return com.ExecuteReader();
        }

        private void button3_Click(object sender, EventArgs e)
        {
            try
            {
                PharmFilter.PharmFilter f3 = new PharmFilter.PharmFilter();

                if (dt_pharm == null)
                {
                    SqlCommand myCommand = pharmolapdw.CreateCommand();
                    myCommand.CommandType = CommandType.StoredProcedure;
                    myCommand.CommandText = @"pas_PharmSel";
                    if (pharmolapdw.State == ConnectionState.Closed) pharmolapdw.Open();
                    dt_pharm = new DataTable();
                    dt_pharm.Load(myCommand.ExecuteReader());
                    foreach (DataColumn dc in dt_pharm.Columns)
                    {
                        if (dc.ColumnName != namechcol) dc.ReadOnly = true;
                        else dc.ReadOnly = false;
                    }
                    pharmolapdw.Close();
                }
                f3.namechcol = namechcol;
                f3.Text = "Фильтр по аптекам";
                f3.sortcoumn = "Аптека";
                f3.dataGridView1.DataSource = dt_pharm.DefaultView;
                f3.ShowDialog();
                f3.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }

        }

        private void button5_Click(object sender, EventArgs e)
        {
            //ExpToExcel ex = new ExpToExcel();
            //ex.ExportToExcel(dataGridView1);
            SaveFileDialog sfd = new SaveFileDialog();
            sfd.FileName = comboBox3.Text;
            sfd.Filter = "Файл CSV | *.csv";
            if (sfd.ShowDialog() == DialogResult.OK)
            {
                SaveDGVToCSVfile(sfd.FileName.ToString(), dt_rep, checkBox6.Checked);
            }
        }

        public static bool SaveDGVToCSVfile(string filename, DataTable table, bool openInExcel)
        {
            try
            {
                StreamWriter sw = new StreamWriter(filename, false, Encoding.Unicode);

                List<int> col_n = new List<int>();
                foreach (DataColumn col in table.Columns)
                {
                    sw.Write(col.ColumnName + "\t");
                    col_n.Add(col.Ordinal);
                }
                sw.WriteLine();
                int x = table.Rows.Count;
                
                //if (table.AllowUserToAddRows) x--;

                for (int i = 0; i < x; i++)
                {
                    for (int y = 0; y < col_n.Count; y++)
                        sw.Write(table.Rows[i].ItemArray[col_n[y]].ToString() + "\t");
                    sw.Write(" \r\n");
                }
                sw.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Ошибка!", MessageBoxButtons.OK, MessageBoxIcon.Stop);
                return false;
            }

            if (openInExcel)
            {
                try
                {
                    Process newProc = Process.Start("excel.exe", "\"" + filename + "\"");
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, "Ошибка!", MessageBoxButtons.OK, MessageBoxIcon.Stop);
                }
            }
            else
                MessageBox.Show("Данные экспортированы");
            return true;
        }

        private void dateTimePicker2_ValueChanged(object sender, EventArgs e)
        {
            if (dateTimePicker2.Value <= dateTimePicker1.Value) dateTimePicker1.Value = dateTimePicker2.Value;
        }

        private void dateTimePicker1_ValueChanged(object sender, EventArgs e)
        {
            if (dateTimePicker2.Value <= dateTimePicker1.Value) dateTimePicker2.Value = dateTimePicker1.Value;
        }

        private void splitContainer2_SplitterMoved(object sender, SplitterEventArgs e)
        {

        }

        private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            try
            {
                checkedListBox1.Items.Clear();
                SqlCommand com = pharmolapdw.CreateCommand();
                com.CommandText = "pas_CitySel";
                DataTable dt_city = new DataTable();
                dt_city.Load(com.ExecuteReader());

                if (dt_city.Rows.Count > 0)
                    foreach (DataRow row in dt_city.Rows)
                    {
                        if (row[3].ToString() == comboBox1.Text)
                        {
                            NewItem ni = new NewItem();
                            ni.ParamID = Convert.ToInt32(row[1]);
                            ni.ParamComment = row[2].ToString();
                            checkedListBox1.Items.Add(ni);
                        }
                    }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void comboBox3_SelectedIndexChanged(object sender, EventArgs e)
        {
            bool vis = false;
            if ((sender as ComboBox).SelectedIndex == 0) vis = false;
            if ((sender as ComboBox).SelectedIndex == 1) vis = true;
            label3.Visible = vis;
            dateTimePicker3.Visible = vis;
        }

        private void button2_Click(object sender, EventArgs e)
        {
            try
            {
                if (comboBox1.SelectedIndex == -1)
                {
                    MessageBox.Show("Не выбрана база и города");
                    comboBox1.Focus();
                }
                else
                {
                    if (dt_suppl == null) dt_suppl = new DataTable();
                    dt_suppl.Clear();
                    DataTable dt_sup_tmp = new DataTable();
                    SqlCommand supcomm = allpricedw.CreateCommand();
                    supcomm.CommandText = "pas_SupplSel";
                    supcomm.CommandTimeout = 60;
                    supcomm.CommandType = CommandType.StoredProcedure;
                    supcomm.Parameters.AddWithValue("@baseid", (comboBox1.SelectedItem as NewItem).ParamID);
                    supcomm.Parameters.Add("@cityid", SqlDbType.Int);
                    if (allpricedw.State == ConnectionState.Closed) allpricedw.Open();

                    if (checkedListBox1.CheckedItems.Count > 0)
                    {
                        for (int i = 0; i < checkedListBox1.CheckedItems.Count; i++)
                        {
                   
                            supcomm.Parameters["@cityid"].Value = (checkedListBox1.CheckedItems[i] as NewItem).ParamID;
                            dt_sup_tmp.Load(supcomm.ExecuteReader());
                        }

                        DataView view = new DataView(dt_sup_tmp);
                        dt_suppl = view.ToTable(true, "Флаг", "Код поставщика", "Поставщик");

                        PharmFilter.PharmFilter form = new PharmFilter.PharmFilter();
                        form.namechcol = namechcol;
                        form.Text = "Фильтр по поставщикам";
                        form.sortcoumn = "Поставщик";
                        form.dataGridView1.DataSource = dt_suppl.DefaultView;
                        form.ShowDialog();
                        listBox2.Items.Clear();
                        foreach (DataRow dr in dt_suppl.Rows)
                        {
                            if (Convert.ToBoolean(dr[namechcol]) == true)
                                listBox2.Items.Add(dr[form.sortcoumn]);
                        }
                        if (listBox2.Items.Count == 0)
                        {
                            listBox2.Items.Add("(Все поставщики)");
                        }
                        form.Close();
                        form = null;
                    }
                    else MessageBox.Show("Не выбран ни один город");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void checkedListBox1_ItemCheck(object sender, ItemCheckEventArgs e)
        {
            dt_suppl = null;
            listBox2.Items.Clear();
            listBox2.Items.Add("(Все поставщики)");
        }

        private void checkBox1_CheckedChanged(object sender, EventArgs e)
        {
            showres = checkBox1.Checked;
        }

    }
}
